from flask import (
    Blueprint,
    render_template,
    request,
    session,
    redirect,
    url_for,
    g,
    jsonify,
    make_response,
    send_from_directory
)
from .models import *
from apps.cms.decorators import login_need
import config
from apps.api.models import User, UserRecord
from utils import restful, common_util, img_path, api_util
from datetime import datetime, date
import os
from .views_cms import bp
from utils import excel


@bp.route('/cms3_test/')
def cms3_test():
    return restful.success()


@bp.route('/send_order_excel/')     # 导出发货订单
@login_need
def send_order_excel():
    directory = os.path.join('utils/excel_data/')
    filename = '发货订单.xls'
    # real_status = request.args.get('real_status')

    sql = "SELECT a.trade_no 订单编号, a.user_id 会员账户, b.`name` 收货人姓名, '中国' 收货人国家, b.province 收货人省, b.city 收货人城市, b.district 收货人县区, b.place 收货人地址, b.phone 收货人移动电话, c.goods_name 商品名称, c.goods_code 商品条码, c.goods_num 数量, c.price `实际销售价（折扣前）` FROM `c_mall_buy_order` a, c_user_address b, c_mall_buy_order_goods c WHERE a.`status` = 1 and a.address_id=b.id and a.id = c.order_id"

    excel.sql_output_excel(sql, filename=directory + filename, sheet_name='发货订单')

    return make_response(
        send_from_directory(directory, filename, as_attachment=True)
    )


@bp.route('/withdraw_excel/')     # 导出提现订单
@login_need
def withdraw_excel():
    directory = os.path.join('utils/excel_data/')
    filename = '提现订单.xls'
    # real_status = request.args.get('real_status')

    sql = "SELECT b.username 用户名, b.phone 手机号, a.price * 0.9 提现金额, if(a.pay_type=3,'银行卡', '其他') 类型, c.pay_name 账户名称, c.real_name 提现姓名, c.pay_card 提现账号, a.create_time 申请时间, a.trade_no 订单号 FROM c_order_withdraw a, c_user b, c_user_pay_account c WHERE a.`status` = 0 AND a.user_id = b.id and c.user_id=b.id and c.pay_type = a.pay_type ORDER BY 申请时间"

    excel.sql_output_excel(sql, filename=directory + filename, sheet_name='提现订单')

    return make_response(
        send_from_directory(directory, filename, as_attachment=True)
    )

